package com.mobilesalon.provider;

import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.mobilesalon.provider.ServiceClientContract.Appointment;
import com.mobilesalon.provider.ServiceClientContract.Service;
import com.mobilesalon.provider.ServiceClientContract.ServiceClient;
import com.mobilesalon.tools.StringUtils;

public class DBHelper {
	private static final String TAG = DBHelper.class.getName();
	public static final String DEVICE_ALERT_ENABLED_ZIP = "DAEZ99";

	private final DBOpenHelper dbOpenHelper;

	private SQLiteDatabase db;

	// SQL requests
	private final String queryFetchAppointmentsBetweenDates;
	private final String queryFetchAppointmentPriceById;
	private final String queryFetchServiceClientsByAppointmentId;
	private final String queryFetchServices;

	// SQL reports
	private final String reportMonthServiceClientGroupByService;
	private final String reportMonthServiceClients;

	public DBHelper(Context context) {
		dbOpenHelper = new DBOpenHelper(context);
		establishDb();

		Log.d(TAG, "Build queries");
		queryFetchAppointmentsBetweenDates = "SELECT *,  (SELECT sum(Price) FROM "
				+ Tables.SERVICE_CLIENT_TABLE + " sc WHERE a." + Appointment._ID + "= sc."
				+ ServiceClient.APPOINTMENT_ID + ") as sum FROM " + Tables.APPOINTMENT_TABLE
				+ " a WHERE a." + ServiceClientContract.Appointment.DATE + " BETWEEN ? AND ?";
		Log.d(TAG, queryFetchAppointmentsBetweenDates);

		queryFetchAppointmentPriceById = "SELECT sum(Price) as price FROM "
				+ Tables.SERVICE_CLIENT_TABLE + " WHERE " + ServiceClient.APPOINTMENT_ID + " = ?";
		Log.d(TAG, queryFetchAppointmentPriceById);

		queryFetchServiceClientsByAppointmentId = "SELECT a.*, s." + Service.SERVICE_NAME
				+ " FROM " + Tables.SERVICE_CLIENT_TABLE + " a, " + Tables.SERVICE_TABLE
				+ " s WHERE a." + ServiceClient.SERVICE_ID + " = s." + Service._ID + " AND "
				+ ServiceClient.APPOINTMENT_ID + " = ?";
		Log.d(TAG, queryFetchServiceClientsByAppointmentId);

		queryFetchServices = "SELECT * FROM " + Tables.SERVICE_TABLE;
		Log.d(TAG, queryFetchServices);

		// SQL reports
		reportMonthServiceClientGroupByService = "SELECT s." + Service._ID + ", s."
				+ Service.SERVICE_NAME + ", count(*) as count, sum(sc." + ServiceClient.PRICE
				+ ") as sum" + " FROM " + Tables.SERVICE_TABLE + " s, "
				+ Tables.SERVICE_CLIENT_TABLE + " sc, " + Tables.APPOINTMENT_TABLE + " app "
				+ " WHERE s." + Service._ID + " = sc." + ServiceClient.SERVICE_ID + " AND app."
				+ Appointment.DATE + " BETWEEN ? AND ? " + " AND app." + Appointment._ID + " = sc."
				+ ServiceClient.APPOINTMENT_ID + " AND app." + Appointment.CALENDAR_OWNER
				+ " in (@calendarOwners@) " + " GROUP BY s." + Service._ID;
		Log.d(TAG, reportMonthServiceClientGroupByService);

		reportMonthServiceClients = "SELECT sc." + ServiceClient._ID + ", a." + Appointment.DATE
				+ ", sc." + ServiceClient.CLIENT_DISPLAY_NAME + ", s." + Service.SERVICE_NAME
				+ ", sc." + ServiceClient.PRICE + " FROM " + Tables.APPOINTMENT_TABLE + " a "
				+ " , " + Tables.SERVICE_CLIENT_TABLE + " sc " + ", " + Tables.SERVICE_TABLE
				+ " s " + "WHERE  a." + Appointment.DATE + " BETWEEN ? AND ? " + " AND a."
				+ Appointment._ID + " = sc." + ServiceClient.APPOINTMENT_ID + " AND sc."
				+ ServiceClient.SERVICE_ID + " = s." + Service._ID + " AND a."
				+ Appointment.CALENDAR_OWNER + " in (@calendarOwners@) ";
		Log.d(TAG, reportMonthServiceClients);
	}

	private void establishDb() {
		if (db == null) {
			db = dbOpenHelper.getWritableDatabase();
		}
	}

	public void cleanup() {
		if (db != null) {
			db.close();
			db = null;
		}
	}

	public SQLiteDatabase getDb() {
		return db;
	}

	public Cursor fetchAppointmentsBetweenDates(String from, String to) {
		Log.d(TAG, queryFetchAppointmentsBetweenDates);
		return this.db.rawQuery(queryFetchAppointmentsBetweenDates, new String[] { from, to });
	}

	public Cursor fetchAppointmentById(long appointmentId) {
		Log.d(TAG, "fetchAppointmentById");
		return this.db.query(Tables.APPOINTMENT_TABLE, null, Appointment._ID + " = ? ",
				new String[] { String.valueOf(appointmentId) }, null, null, null);

	}

	public Cursor fetchAppointmentPriceById(long appointmentId) {
		Log.d(TAG, "queryFetchAppointmentPriceById");
		return this.db.rawQuery(queryFetchAppointmentPriceById,
				new String[] { String.valueOf(appointmentId) });

	}

	public Cursor fetchServiceClientsByAppointmentId(long appointmentId) {
		Log.d(TAG, "queryFetchServiceClientsByAppointmentId");
		return this.db.rawQuery(queryFetchServiceClientsByAppointmentId,
				new String[] { String.valueOf(appointmentId) });
	}

	public int deleteServiceClient(long serviceClientId) {
		Log.d(TAG, "deleteServiceClient");
		return this.db.delete(Tables.SERVICE_CLIENT_TABLE, ServiceClient._ID + " = ? ",
				new String[] { String.valueOf(serviceClientId) });
	}

	public Cursor fetchServices() {
		Log.d(TAG, "queryFetchServices");
		return this.db.rawQuery(queryFetchServices, null);
	}

	public Cursor reportMonthServiceClientGroupByService(String from, String to,
			List<String> calendarOwners) {
		Log.d(TAG, "reportMonthServiceClientGroupByService");
		String calendarOwnersString = "'"
				+ StringUtils.arrayToString((String[]) calendarOwners.toArray(), "','") + "'";

		String reportLocal = reportMonthServiceClientGroupByService.replace("@calendarOwners@",
				calendarOwnersString);

		return this.db.rawQuery(reportLocal, new String[] { from, to });
	}

	public Cursor reportMonthServiceClients(String from, String to, List<String> calendarOwners) {
		Log.d(TAG, "reportMonthServiceClients");
		String calendarOwnersString = "'"
				+ StringUtils.arrayToString((String[]) calendarOwners.toArray(), "','") + "'";

		String reportLocal = reportMonthServiceClients.replace("@calendarOwners@",
				calendarOwnersString);

		return this.db.rawQuery(reportLocal, new String[] { from, to });
	}

}